IF OBJECT_ID('dbo.fnParseSubcontractorName') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.fnParseSubcontractorName
    IF OBJECT_ID('dbo.fnParseSubcontractorName') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION dbo.fnParseSubcontractorName >>>'
    ELSE
        PRINT '<<< DROPPED FUNCTION dbo.fnParseSubcontractorName >>>'
END
go

-- =======================================================================
-- Function Name: dbo.fnParseSubcontractorName()
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 12/11/2008
--
-- Description: Parses string up to the first instance of a date.
--             
--
-- Parameters:  DescriptiveName    VARCHAR(50) 
--              
-- Returns:     VARCHAR(50) 
--
-- SampleCall:
-- SELECT @SubName = fnParseSubcontractorName ( 'Vanderbilt U Med Ctr 08/01/2008-07/31/2009' )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 12/11/08    KC        Created
-- 12/15/08	   CMB       Added check for various "NA" fields
-- 08/27/2009	SP		 Allowing NULL values to work
-- =======================================================================
CREATE FUNCTION dbo.fnParseSubcontractorName(
     @DescriptiveName VARCHAR(50)
)
     RETURNS VARCHAR(50)
AS
BEGIN
     DECLARE   @ParsedName    VARCHAR(50),
               @Word          VARCHAR(50),
               @Char          CHAR(1),
               @Pos           INT

     -- Default
     SET  @ParsedName = ''
     SET  @Word = ''
     SET  @Pos = -1
          
     
     SELECT @DescriptiveName=ISNULL(@DescriptiveName,'UNKNOWN') -- added by Ponraja Chandran on 11/04/2009        
     IF @DescriptiveName IN ('','0') SET @DescriptiveName = 'UNKNOWN'  -- added by Surajit Paul on 08/27/2009
          
     WHILE @Pos != 0
     BEGIN
          -- Find first instance of space or '-'
          SET @Char = ' '
          SET @Pos = CHARINDEX(@Char,@DescriptiveName)
          IF @Pos = 0
          BEGIN
               SET @Char = '-'
               SET @Pos = CHARINDEX('-',@DescriptiveName)
          END

          -- Did we find anything?
          IF @Pos != 0
          BEGIN
               -- Get word and see if it's a date
               SELECT    @Word = LEFT(@DescriptiveName,@Pos-1),
                         @DescriptiveName = SUBSTRING(@DescriptiveName,@Pos+1,50)
                         
               IF ISDATE(@Word) = 1     -- Done, we already have the name
               BEGIN
                    SET  @ParsedName = RTRIM(@ParsedName)
                    SET  @Pos = 0
               END
               ELSE
                    SET  @ParsedName = @ParsedName + @Word + @Char
          END
     END
     
     IF LEN(@DescriptiveName) > 0 AND ISDATE(@DescriptiveName) = 0
          SET  @ParsedName = @ParsedName + @DescriptiveName
     
     IF @ParsedName = ''
          SET @ParsedName = @DescriptiveName
         
     SET @ParsedName =RTRIM(@ParsedName)    
     SET @ParsedName = REPLACE(@ParsedName,' NA-NA','')
     SET @ParsedName = REPLACE(@ParsedName,' NA-','')
     SET @ParsedName = REPLACE(@ParsedName,' NA - NA','')
     SET @ParsedName = REPLACE(@ParsedName,' NA -','')
     
     RETURN @ParsedName
     
     

END
go
IF OBJECT_ID('dbo.fnParseSubcontractorName') IS NOT NULL
    PRINT '<<< CREATED FUNCTION dbo.fnParseSubcontractorName >>>'
ELSE
    PRINT '<<< FAILED CREATING FUNCTION dbo.fnParseSubcontractorName >>>'
go
